import pymysql
# =============================================================================
# # 打开数据库连接
# db = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='spring', charset='utf8')
#
# # 使用 cursor() 方法创建一个游标对象 cursor
# cursor = db.cursor()
#
# # 使用 execute()  方法执行 SQL 查询
# cursor.execute("SELECT * from bookstore")
# # 使用 fetchone() 方法获取数据.
# books = cursor.fetchall()
# for book in books:
#     print(book)
#
# # 关闭数据库连接
# db.close()
# =============================================================================



# =============================建表=============================================
# # 打开数据库连接
# db = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='test', charset='utf8')
# # 使用 cursor() 方法创建一个游标对象 cursor
# cursor = db.cursor()
# # 使用 execute()  方法执行 SQL 查询
# sql = '''
# CREATE TABLE `movie250`  (
#   `id` int NOT NULL AUTO_INCREMENT,
#   `info_link` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
#   `pic_link` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
#   `cname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
#   `ename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
#   `score` decimal(10, 1) NULL DEFAULT NULL,
#   `rated` decimal(10, 0) NULL DEFAULT NULL,
#   `instroduction` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
#   `info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
#   PRIMARY KEY (`id`) USING BTREE
# )
# '''
# try:
#     # 执行创建表的sql
#     cursor.execute(sql)
#     print("创建表成功")
# except Exception as e:
#     print(e)
#     print("创建表失败")
# finally:
#     # 关闭游标连接
#     cursor.close()
#     # 关闭数据库连接
#     db.close()
# =============================================================================


# ================================插入数据==========================================
# # 创建连接
# con = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456', db='test', charset='utf8')
# # 创建游标对象
# cur = con.cursor()
#
# # 编写插入数据的sql
# # 第一种
# sql0 = "INSERT INTO `movie250` (info_link,pic_link,cname,ename,score,rated,instroduction,info) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
# # 第二种
# data = ['https://movie.douban.com/subject/1292052/', 'https://img2.doubanio.com/view/photo/s_ratio_poster/public/p480747492.jpg', '肖申克的救赎', '\xa0\xa0The Shawshank Redemption', '9.7', '2493541', '希望让人自由', '导演: 弗兰克·德拉邦特 Frank Darabont\xa0\xa0\xa0主演: 蒂姆·罗宾斯 Tim Robbins  ... 1994\xa0 \xa0美国\xa0 \xa0犯罪 剧情']
# for index in range(len(data)):
#     if index == 4 or index == 5:
#         # print(type(data[index]))
#         continue
#     data[index] = '"'+ data[index]+'"'
# sql1 = '''
#         insert into movie250(info_link,pic_link,cname,ename,score,rated,instroduction,info)
#         values(%s)'''%",".join(data)
# # print(sql1)
#
# try:
#     # 执行sql
#     # 第一种插入方式
#     # cur.execute(sql0,('https://movie.douban.com/subject/1292052/', 'https://img2.doubanio.com/view/photo/s_ratio_poster/public/p480747492.jpg', '肖申克的救赎', '\xa0\xa0The Shawshank Redemption', '9.7', '2493541', '希望让人自由', '导演: 弗兰克·德拉邦特 Frank Darabont\xa0\xa0\xa0主演: 蒂姆·罗宾斯 Tim Robbins  ... 1994\xa0 \xa0美国\xa0 \xa0犯罪 剧情'))
#
#     # 第二种插入方式
#     cur.execute(sql1)
#     con.commit()
#     print("插入数据成功")
# except Exception as e:
#     print(e)
#     con.rollback()
#     print("插入数据失败")
# finally:
#     cur.close()
#     con.close()

# =============================================================================

# 增删改查 后续补齐

